*===============================================================================
* Project title		Savings Accounts to Borrow Less	
* PIs			Felipe Kast, Dina Pomeranz 
* File name		2f_tables_analysis_savings.do
*-------------------------------------------------------------------------------
* Description		Creates savings tables 
* Outputs		Tables 5, A13, A14, A15 
* Spaces per tab 	8 
*===============================================================================

*===============================================================================
*TOC
*1)	Prepare data for analysis
*2)	Tables
*2.1)	Savings table (admin data)
*2.2)	Savings table (self-reported data)
*===============================================================================

*===============================================================================
* Section 1 - Prepare data for analysis
*===============================================================================

clear all
global path "~/file_server/project_savings/2_shared/impact/do/November2019"	// Path to processed data
global results "$path/results"		
cd "$results"									// Change to directory to store results
set more off

*Loading processed data
	use $path/datasets/impactDatabase.dta, clear

*===============================================================================
* Section 2 - Tables 
*===============================================================================
* Section 2.1 - Table on Savings
*
	foreach y in 	PrallSavingsAd		IHSallSavingsAd 		/// Panel A : Col1, Col2, Col6 are created here 
			PrnetSavingsAd		IHSnetSavingsAd			/// Panel B : Col2, Col6
			PrchangeTotalWealthAd	IHSchangeTotalWealthAd		/// Panel C : Col2, Col6
			{
					
	//locals for column numbers	
	*--------------------------			
	if "`y'" 		== "PrallSavingsAd" | "`y'" == "PrnetSavingsAd" | "`y'" == "PrchangeTotalWealthAd"  {
	local c 		= 2
	}
	else if "`y'"		== "IHSallSavingsAd" |"`y'" ==  "IHSnetSavingsAd" | "`y'" == "IHSchangeTotalWealthAd" {
	local c 		= 6
	}
	else if "`y'"		== "savingsCatAd"	{
	local c 		= 1
	}
	
	//locals for panels
	*-------------------
	if "`y'" 		== "savingsCatAd" | "`y'" == "PrallSavingsAd" | "`y'" == "IHSallSavingsAd"  {
	local p 		= 1 // or A in tables
	}
	else if "`y'"		== "PrnetSavingsAd" | "`y'" == "IHSnetSavingsAd"  {
	local p 		= 2 //or B in tables
	}
	else if "`y'"		== "PrchangeTotalWealthAd" | "`y'" == "IHSchangeTotalWealthAd" {
	local p			= 3 // or C in tables
	}
			
	reg d.`y' if inSample == 1						// Mark subjects nonmissing in both periods
	egen t1 = max(e(sample)), by(idBaseSurvey)		
	
	reghdfe `y' c.accountAccess#c.post post if t1 ==1,			/// Regressing savings outcome variables on treatment x post 
	abs(idBaseSurvey) vce(cluster groupId)					// Individual FE, Stratum FE x Post, SEs clustered at group level

	matrix list r(table) //Shows contents of stored matrix
	mat m=r(table)
	
	
	/*coef */		local P`p'C`c'R1: di %12.3fc m[1,1]
	/*s.e.*/		local P`p'C`c'R2: di %12.3fc m[2,1]
				local P`p'C`c'R2: di trim("`P`p'C`c'R2'")
	/*(s.e.) */		local P`p'C`c'R2="(`P`p'C`c'R2')"
	/*p-val*/		local P`p'C`c'pv: di %12.3fc m[4,1]

	distinct idBaseSurvey if e(sample)
	gen `y'_s = e(sample) //marks observations used in the regression 
	
	/*individuals*/		local P`p'C`c'R4: di %12.0fc `r(ndistinct)'
	/*obs*/			local P`p'C`c'R5: di %12.0fc `r(N)'
	
	sum `y' if accountAccess == 0 & post == 1 // control's mean outcome
	/*controlmean*/		local P`p'C`c'R3: di %12.3fc `r(mean)'
	/*Individual fe*/	local P`p'C`c'R6 = "Yes"
	/*StratumXPost fe*/	local P`p'C`c'R7 = "Yes"
	
	drop t1
	di in red "Panel`p'Col`c'"
}

*----------------------------------------
*This is a different loop because of decimal points in beta here being 0 not 3.
	foreach y in	w5_allSavingsAd		w1_allSavingsAd		allSavingsAd		/// Panel A : Col3, Col4, Col5 are created here 
			w5_netSavingsAd 	w1_netSavingsAd		netSavingsAd 		/// Panel B : Col3, Col4, Col5
			w5_changeTotalWealthAd w1_changeTotalWealthAd 	changeTotalWealthAd	/// Panel C : Col3, Col4, Col5
			{
					
	//locals for column numbers	
	*--------------------------			
	if "`y'" 		== "w5_allSavingsAd" | "`y'" == "w5_netSavingsAd" | "`y'" == "w5_changeTotalWealthAd" 		{
	local c 		= 3
	}
	else if "`y'"		== "w1_allSavingsAd" |"`y'" ==  "w1_netSavingsAd" | "`y'" == "w1_changeTotalWealthAd" 		{
	local c 		= 4
	}
	else if "`y'"		== "allSavingsAd" |"`y'" ==  "netSavingsAd" | "`y'" == "changeTotalWealthAd" 			{
	local c 		= 5
	}
	
	//locals for panels
	*-------------------
	if "`y'" 		== "w5_allSavingsAd" | "`y'" == "w1_allSavingsAd" | "`y'" == "allSavingsAd"			{
	local p 		= 1 // or A in tables
	}
	else if "`y'"		== "w5_netSavingsAd" | "`y'" == "w1_netSavingsAd" |  "`y'" == "`netSavingsAd'"			{
	local p 		= 2 //or B in tables
	}
	else if "`y'"		== "w5_changeTotalWealthAd" | "`y'" == "w1_changeTotalWealthAd" |"`y'" == "changeTotalWealthAd"	{
	local p			= 3 // or C in tables
	}
			
	reg d.`y'  if inSample == 1						// Mark subjects nonmissing in both periods
	egen t1 		= max(e(sample)), by(idBaseSurvey)		
	
	reghdfe `y' c.accountAccess#c.post post if t1 ==1,			///
	abs(idBaseSurvey) vce(cluster groupId)

	matrix list r(table) //Shows contents of stored matrix
	mat m=r(table)
	
	
	/*coef */		local P`p'C`c'R1: di %12.0fc m[1,1]
	/*s.e.*/		local P`p'C`c'R2: di %12.0fc m[2,1]
				local P`p'C`c'R2: di trim("`P`p'C`c'R2'")
	/*(s.e.) */		local P`p'C`c'R2="(`P`p'C`c'R2')"
	/*p-val*/		local P`p'C`c'pv: di %12.3fc m[4,1]

	distinct idBaseSurvey if e(sample)
	
	/*individuals*/		local P`p'C`c'R4: di %12.0fc `r(ndistinct)'
	/*obs*/			local P`p'C`c'R5: di %12.0fc `r(N)'
	
	sum `y' if accountAccess == 0 & post == 1 // control's mean outcome
	/*controlmean*/		local P`p'C`c'R3: di %12.0fc `r(mean)'
	/*Individual fe*/	local P`p'C`c'R6 = "Yes"
	/*StratumXPost fe*/	local P`p'C`c'R7 = "Yes"
	
	drop t1
	di in red "Panel`p'Col`c'"
}

// For savingsCat 

	local y = "savingsCatAd"
					
	//locals for column numbers	
	*--------------------------			
	local c 		= 1
	
	//locals for panels
	*-------------------
	local p 		= 1 
				
	reg d.`y' if inSample == 1						// Mark subjects nonmissing in both periods
	egen t1 		= max(e(sample)), by(idBaseSurvey)		
	
	reghdfe `y' c.accountAccess#c.post post if t1 ==1 & PrallSavingsAd_s == 1,	/// added extra condition for e(sample)
	abs(idBaseSurvey) vce(cluster groupId)

	matrix list r(table) //Shows contents of stored matrix
	mat m=r(table)
	
	
	/*coef */		local P`p'C`c'R1: di %12.3fc m[1,1]
	/*s.e.*/		local P`p'C`c'R2: di %12.3fc m[2,1]
				local P`p'C`c'R2: di trim("`P`p'C`c'R2'")
	/*(s.e.) */		local P`p'C`c'R2="(`P`p'C`c'R2')"
	/*p-val*/		local P`p'C`c'pv: di %12.3fc m[4,1]

	distinct idBaseSurvey if e(sample)
	
	/*individuals*/		local P`p'C`c'R4: di %12.0fc `r(ndistinct)'
	/*obs*/			local P`p'C`c'R5: di %12.0fc `r(N)'
	
	sum `y' if accountAccess == 0 & post == 1 // control's mean outcome
	/*controlmean*/		local P`p'C`c'R3: di %12.3fc `r(mean)'
	/*Individual fe*/	local P`p'C`c'R6 = "Yes"
	/*StratumXPost fe*/	local P`p'C`c'R7 = "Yes"
	
	drop t1
	di in red "Panel`p'Col`c'"
	

*** Put stars in regression coefficients
	forvalue c=1/1	{
	forvalue p=1/1	{
	*stars
	if `P`p'C`c'pv'<=0.10 & `P`p'C`c'pv'>0.05 {
	local P`p'C`c'R1="$`P`p'C`c'R1'^{*}$"
	}
	else if  `P`p'C`c'pv'<=0.05 &  `P`p'C`c'pv'>0.01 {
	local P`p'C`c'R1="$`P`p'C`c'R1'^{**}$"
	}
	else if  `P`p'C`c'pv'<=0.01 { 
	local P`p'C`c'R1="$`P`p'C`c'R1'^{***}$"
	}
	}
	}
	
*** Put stars in regression coefficients
	forvalue c=2/6	{
	forvalue p=1/3	{
	*stars
	if `P`p'C`c'pv'<=0.10 & `P`p'C`c'pv'>0.05 {
	local P`p'C`c'R1="$`P`p'C`c'R1'^{*}$"
	}
	else if  `P`p'C`c'pv'<=0.05 &  `P`p'C`c'pv'>0.01 {
	local P`p'C`c'R1="$`P`p'C`c'R1'^{**}$"
	}
	else if  `P`p'C`c'pv'<=0.01 { 
	local P`p'C`c'R1="$`P`p'C`c'R1'^{***}$"
	}
	}
	}	
	
//Add empty locals for Col2 Panel B and Col3 Panel C (as Categories for savings is missing for netsaving and netwealth)
	local c = 1
	forval p = 2/3	{
	forval r = 1/7	{
	local P`p'C`c'R`r' = ""
	}
	}
	
// Row titles

	local lbl_1 "Account $\times$ post"	
	local lbl_2 ""
	local lbl_3 "Control mean"
	local lbl_4 "Individuals"
	local lbl_5 "Observations"

	local lbl_6 "Individual FE"
	local lbl_7 "Stratum FE $\times$ post"

* Version 1 - 6 Columns 
//Table code:
	texdoc init $results/tab_savings_admin.tex, replace force		// Note: Need to include tablurax environment in latex
	tex \\ \\ [-1.5ex]
	tex \hline\hline \\ [-1.5ex]
	tex 	& (1) 				& (2)				  & (3) 			& (4) 				 & (5)	 		    & (6)		\\
	tex {}	& Categories 	& Probability of any savings		& Amounts (winsorized at 5\%) 	& Amounts (winsorized at 1\%) 	 & Amounts (non-winsorized) & IHS of amounts	\\ 
	tex \hline
	tex \\ 
	tex \multicolumn{2}{l}{\textbf{Panel A:} Total Financial Savings} & & & & & \\
	forvalue t=1/5 {
	tex `lbl_`t'' & `P1C1R`t'' & `P1C2R`t''	 & `P1C3R`t'' 	& `P1C4R`t'' 	& `P1C5R`t'' 	& `P1C6R`t'' 	 \\
	}
	tex \\
	tex \multicolumn{2}{l}{\textbf{Panel B:} Net Total Financial Savings (incl. Borrowing)}  & & & & & \\
	forvalue t=1/5 {
	tex `lbl_`t'' & `P2C1R`t'' & `P2C2R`t''	 & `P2C3R`t'' 	& `P2C4R`t'' 	& `P2C5R`t'' 	& `P2C6R`t'' 	 \\
	}
	tex \\
	tex \multicolumn{3}{l}{\textbf{Panel C:} Total Financial Assets (incl. Borrowing and Lending)} & & & & \\
	forvalue t=1/5 {
	tex `lbl_`t'' & `P3C1R`t'' & `P3C2R`t''	 & `P3C3R`t'' 	& `P3C4R`t'' 	& `P3C5R`t'' 	& `P3C6R`t'' 	 \\
	}
	tex \\
	tex  \hline 
	foreach t in 6 {
	tex `lbl_`t'' & `P2C1R`t'' & `P2C2R`t'' & `P2C3R`t'' & `P2C4R`t'' & `P2C5R`t'' & `P2C6R`t'' \\
	}
	tex  \hline\hline \\ [-1.5ex]
	texdoc close
	
* Version 2 - 3 Columns 
//Table code:
	texdoc init $results/tab_savings_admin_main.tex, replace force		// Note: Need to include tablurax environment in latex	
	tex \\ \\ [-1.5ex]
	tex \hline\hline \\ [-1.5ex]
	tex 	& (1) 				& (2)				  & (3) 					\\
	tex {}	& Probability of any savings 	& Amounts (winsorized at 5\%) 	 & IHS of amounts	\\ 
	tex \hline
	tex \\
	tex \multicolumn{2}{l}{\textbf{Panel A:} Total Financial Savings} & &  \\
	forvalue t=1/5 {
	tex `lbl_`t'' & `P1C2R`t'' & `P1C3R`t'' & `P1C6R`t'' 	 \\
	}
	tex \\
	tex \multicolumn{2}{l}{\textbf{Panel B:} Net Total Financial Savings (incl. Borrowing)}  & &  \\	
	forvalue t=1/5 {
	tex `lbl_`t'' & `P2C2R`t'' & `P2C3R`t''  & `P2C6R`t'' 	 \\
	}
	tex \\
	tex \multicolumn{3}{l}{\textbf{Panel C:} Total Financial Assets (incl. Borrowing and Lending)} &  \\
	forvalue t=1/5 {
	tex `lbl_`t'' & `P3C2R`t'' & `P3C3R`t'' & `P3C6R`t'' 	 \\
	}
	tex \\ 
	tex \hline 
	foreach t in 6 {
	tex `lbl_`t'' & `P2C2R`t'' & `P2C3R`t'' & `P2C6R`t'' \\
	}
	tex  \hline\hline \\ [-1.5ex]
	texdoc close
		
*-------------------------------------------------------------------------------
*Table 2.2 Savings (only self reported)
*-------------------------------------------------------------------------------
*2.2.1 Table content 

	foreach y in savingsCat 	PrallSavings 		IHSallSavings  		/// Panel A : Col1, Col2, Col6 are created here 
					PrnetSavings 		IHSnetSavings 		/// Panel B : Col2, Col6
					PrchangeTotalWealth 	IHSchangeTotalWealth 	/// Panel C : Col2, Col6
					{
					
	//locals for column numbers	
	*--------------------------			
	if "`y'" 		== "PrallSavings" | "`y'" == "PrnetSavings" | "`y'" == "PrchangeTotalWealth"  	{
	local c 		= 2
	}
	else if "`y'"		== "IHSallSavings" |"`y'" ==  "IHSnetSavings" | "`y'" == "IHSchangeTotalWealth" {
	local c 		= 6
	}
	else if "`y'"		== "savingsCat"									{
	local c 		= 1
	}
	
	//locals for panels
	*-------------------
	if "`y'" 		== "savingsCat" | "`y'" == "PrallSavings" | "`y'" == "IHSallSavings"  		{
	local p 		= 1 // or A in tables
	}
	else if "`y'"		== "PrnetSavings" | "`y'" == "IHSnetSavings"  					{
	local p 		= 2 //or B in tables
	}
	else if "`y'"		== "PrchangeTotalWealth" | "`y'" == "IHSchangeTotalWealth" 			{
	local p			= 3 // or C in tables
	}
			
	reg d.`y'								// Mark subjects nonmissing in both periods
	egen t1 		= max(e(sample)), by(idBaseSurvey)		
	
	reghdfe `y' c.accountAccess#c.post post if t1 ==1,			///
	abs(idBaseSurvey) vce(cluster groupId)

	matrix list r(table) //Shows contents of stored matrix
	mat m=r(table)
	
	
	/*coef */		local P`p'C`c'R1: di %12.3fc m[1,1]
	/*s.e.*/		local P`p'C`c'R2: di %12.3fc m[2,1]
				local P`p'C`c'R2: di trim("`P`p'C`c'R2'")
	/*(s.e.) */		local P`p'C`c'R2="(`P`p'C`c'R2')"
	/*p-val*/		local P`p'C`c'pv: di %12.3fc m[4,1]

	distinct idBaseSurvey if e(sample)
	
	/*individuals*/		local P`p'C`c'R4: di %12.0fc `r(ndistinct)'
	/*obs*/			local P`p'C`c'R5: di %12.0fc `r(N)'
	
	sum `y' if accountAccess == 0 & post == 1 // control's mean outcome
	/*controlmean*/		local P`p'C`c'R3: di %12.3fc `r(mean)'
	/*Individual fe*/	local P`p'C`c'R6 = "Yes"
	/*StratumXPost fe*/	local P`p'C`c'R7 = "Yes"
	
	drop t1
	di in red "Panel`p'Col`c'"
}

*----------------------------------------
*This is a different loop bc of decimal points in beta here being 0 not 3.
	foreach y in	w5_allSavings		w1_allSavings		allSavings		/// Panel A : Col3, Col4, Col5 are created here 
			w5_netSavings 	w1_netSavings		netSavings 			/// Panel B : Col3, Col4, Col5
			w5_changeTotalWealth w1_changeTotalWealth 	changeTotalWealth	/// Panel C : Col3, Col4, Col5
			{
					
	//locals for column numbers	
	*--------------------------			
	if "`y'" 		== "w5_allSavings" | "`y'" == "w5_netSavings" | "`y'" == "w5_changeTotalWealth" 		 {
	local c 		= 3
	}
	else if "`y'"		== "w1_allSavings" |"`y'" ==  "w1_netSavings" | "`y'" == "w1_changeTotalWealth" 		{
	local c 		= 4
	}
	else if "`y'"		== "allSavings" |"`y'" ==  "netSavings" | "`y'" == "changeTotalWealth" 			{
	local c 		= 5
	}
	
	//locals for panels
	*-------------------
	if "`y'" 		== "w5_allSavings" | "`y'" == "w1_allSavings" | "`y'" == "allSavings"			{
	local p 		= 1 // or A in tables
	}
	else if "`y'"		== "w5_netSavings" | "`y'" == "w1_netSavings" |  "`y'" == "`netSavings'"				{
	local p 		= 2 //or B in tables
	}
	else if "`y'"		== "w5_changeTotalWealth" | "`y'" == "w1_changeTotalWealth" |"`y'" == "changeTotalWealth"	{
	local p			= 3 // or C in tables
	}
			
	reg d.`y'								// Mark subjects nonmissing in both periods
	egen t1 		= max(e(sample)), by(idBaseSurvey)		
	
	reghdfe `y' c.accountAccess#c.post post if t1 ==1,			///
	abs(idBaseSurvey) vce(cluster groupId)

	matrix list r(table) //Shows contents of stored matrix
	mat m=r(table)
	
	
	/*coef */		local P`p'C`c'R1: di %12.0fc m[1,1]
	/*s.e.*/		local P`p'C`c'R2: di %12.0fc m[2,1]
				local P`p'C`c'R2: di trim("`P`p'C`c'R2'")
	/*(s.e.) */		local P`p'C`c'R2="(`P`p'C`c'R2')"
	/*p-val*/		local P`p'C`c'pv: di %12.3fc m[4,1]

	distinct idBaseSurvey if e(sample)
	
	/*individuals*/		local P`p'C`c'R4: di %12.0fc `r(ndistinct)'
	/*obs*/			local P`p'C`c'R5: di %12.0fc `r(N)'
	
	sum `y' if accountAccess == 0 & post == 1 // control's mean outcome
	/*controlmean*/		local P`p'C`c'R3: di %12.0fc `r(mean)'
	/*Individual fe*/	local P`p'C`c'R6 = "Yes"
	/*StratumXPost fe*/	local P`p'C`c'R7 = "Yes"
	
	drop t1
	di in red "Panel`p'Col`c'"
}

*** Put stars in regression coefficients
	forvalue c=1/1	{
	forvalue p=1/1	{
	*stars
	if `P`p'C`c'pv'<=0.10 & `P`p'C`c'pv'>0.05 {
	local P`p'C`c'R1="$`P`p'C`c'R1'^{*}$"
	}
	else if  `P`p'C`c'pv'<=0.05 &  `P`p'C`c'pv'>0.01 {
	local P`p'C`c'R1="$`P`p'C`c'R1'^{**}$"
	}
	else if  `P`p'C`c'pv'<=0.01 { 
	local P`p'C`c'R1="$`P`p'C`c'R1'^{***}$"
	}
	}
	}
	
*** Put stars in regression coefficients
	forvalue c=2/6	{
	forvalue p=1/3	{
	*stars
	if `P`p'C`c'pv'<=0.10 & `P`p'C`c'pv'>0.05 {
	local P`p'C`c'R1="$`P`p'C`c'R1'^{*}$"
	}
	else if  `P`p'C`c'pv'<=0.05 &  `P`p'C`c'pv'>0.01 {
	local P`p'C`c'R1="$`P`p'C`c'R1'^{**}$"
	}
	else if  `P`p'C`c'pv'<=0.01 { 
	local P`p'C`c'R1="$`P`p'C`c'R1'^{***}$"
	}
	}
	}	
	
//Add empty locals for Col2 Panel B and Col3 Panel C (as Categories for savings is missing for netsaving and netwealth)
	local c = 1
	forval p = 2/3	{
	forval r = 1/7	{
	local P`p'C`c'R`r' = ""
	}
	}
	
// Row titles

	local lbl_1 "Account $\times$ post"	
	local lbl_2 ""
	local lbl_3 "Control mean"
	local lbl_4 "Individuals"
	local lbl_5 "Observations"

	local lbl_6 "Individual FE"
	local lbl_7 "Stratum FE $\times$ post"

//Table code:
	texdoc init $results/tab_savings_self.tex, replace force		// Note: Need to include tablurax environment in latex	
	tex \\ \\ [-1.5ex]
	tex \hline\hline \\ [-1.5ex]
	tex 	& (1) 				& (2)				  & (3) 			& (4) 				 & (5)	 		    & (6)		\\
	tex {}	& Categories 	& Probability of any savings		& Amounts (winsorized at 5\%) 	& Amounts (winsorized at 1\%) 	 & Amounts (non-winsorized) & IHS of amounts	\\ 
	tex \hline
	tex \\ 
	tex \multicolumn{2}{l}{\textbf{Panel A:} Total Financial Savings} & & & & & \\
	forvalue t=1/5 {
	tex `lbl_`t'' & `P1C1R`t'' & `P1C2R`t''	 & `P1C3R`t'' 	& `P1C4R`t'' 	& `P1C5R`t'' 	& `P1C6R`t'' 	 \\
	}
	tex \\
	tex \multicolumn{2}{l}{\textbf{Panel B:} Net Total Financial Savings (incl. Borrowing)}  & & & & & \\
	forvalue t=1/5 {
	tex `lbl_`t'' & `P2C1R`t'' & `P2C2R`t''	 & `P2C3R`t'' 	& `P2C4R`t'' 	& `P2C5R`t'' 	& `P2C6R`t'' 	 \\
	}
	tex \\
	tex \multicolumn{3}{l}{\textbf{Panel C:} Total Financial Assets (incl. Borrowing and Lending)} & & & & \\
	forvalue t=1/5 {
	tex `lbl_`t'' & `P3C1R`t'' & `P3C2R`t''	 & `P3C3R`t'' 	& `P3C4R`t'' 	& `P3C5R`t'' 	& `P3C6R`t'' 	 \\
	}
	tex \\ 
	tex  \hline 
	foreach t in 6 {
	tex `lbl_`t'' & `P2C1R`t'' & `P2C2R`t'' & `P2C3R`t'' & `P2C4R`t'' & `P2C5R`t'' & `P2C6R`t'' \\
	}
	tex  \hline\hline \\ [-1.5ex]
	texdoc close

